from flask import Flask, g
import sqlite3


app = Flask(__name__)


@app.route("/init_db")
def init_db():

    connection = getattr(g, "_database", None)
    if connection is None:
        connection = sqlite3.connect("database.db")
        g._database = connection

    with open("schema.sql") as f:
        connection.executescript(f.read())

    cur = connection.cursor()

    cur.execute(
        "INSERT INTO users (username,email) VALUES (?, ?)",
        ("小明", "123@qq.com"),
    )

    cur.execute(
        "INSERT INTO users (username,email) VALUES (?, ?)",
        ("小张", "456@qq.com"),
    )
    return "db init complete"


@app.route("/user/<uid>")
def query(uid):

    connection = getattr(g, "_database", None)
    if connection is None:
        connection = sqlite3.connect("database.db")
        g._database = connection

    cur = connection.cursor()
    res = cur.execute(
        "select * from users where user_id=?",
        (uid),
    )
    res = res.fetchall()
    print(res[0])
    return f"username: {res[0][1]},email: {res[0][2]}"


@app.teardown_request
def close_conncection(exception):
    print("closing")
    connection = getattr(g, "_database", None)
    if connection is not None:
        connection.commit()
        connection.close()


if __name__ == "__main__":
    app.run(debug=True, port=5011)
